System Parameters¶

Scope of Database Server Configuration¶

CUBRID consists of the database server, the broker and the CUBRID Manager. Each component has its configuration file. The system parameter configuration file for the database server is cubrid.conf located in the $CUBRID/conf directory. System parameters configured in cubrid.conf affect overall performance and operation of the database system. Therefore, it is very important to understand the database server configuration.

The CUBRID database server has a client/server architecture. To be more specific, it is divided into a database server process linked to the server library and the broker process linked to the client library. The server process manages the database storage structure and provides concurrency and transaction functionalities. The client process prepares for query execution and manages object/schema.

System parameters for the database server, which can be set in the cubrid.conf file, are classified into a client parameter, a server parameter and a client/server parameter according to the range to which they are applied. A client parameter is only applied to client processes such as the broker. A server parameter affects the behaviors of the server processes. A client/server parameter must be applied to both server and client.

Changing Database Server Configuration¶

SET SYSTEM PARAMETERS 'parameter_name=value [{; name=value}...]'

SET SYSTEM PARAMETERS 'index_scan_in_oid_order=1; csql_history_num=70';

SET SYSTEM PARAMETERS 'lock_timeout=DEFAULT';

csql> ;se block_ddl_statement=1
=== Set Param Input ===
block_ddl_statement=1

cubrid.conf Configuration File and Default Parameters¶

CUBRID consists of the database server, the broker and the CUBRID Manager. The name of the configuration file for each component is as follows. These files are all located in the $CUBRID/conf directory.

• Database server configuration file: cubrid.conf
• Broker configuration file: cubrid_broker.conf
• CUBRID Manager server configuration file: cm.conf

cubrid.conf is a configuration file that sets system parameters for the CUBRID database server and determines overall performance and operation of the database system. In the cubrid.conf file, some important parameters needed for system installation are provided, having their default values.

.....
[common]
.....
sort_buffer_size=2M
.....
[standalone]

sort_buffer_size=256M
.....

# Copyright (C) 2008 Search Solution Corporation. All rights reserved by Search Solution.
#
# $Id$
#
# cubrid.conf#

# For complete information on parameters, see the CUBRID
# Database Administration Guide chapter on System Parameters

# Service section - a section for 'cubrid service' command
[service]

# The list of processes to be started automatically by 'cubrid service start' command
# Any combinations are available with server, broker and manager.
service=server,broker,manager

# The list of database servers in all by 'cubrid service start' command.
# This property is effective only when the above 'service' property contains 'server' keyword.
#server=demodb,testdb

# Common section - properties for all databases
# This section will be applied before other database specific sections.
[common]

# Read the manual for detailed description of system parameters
# Manual > System Configuration > Database Server Configuration > Default Parameters

# Size of data buffer are using K, M, G, T unit
data_buffer_size=512M

# Size of log buffer are using K, M, G, T unit
log_buffer_size=256M

# Size of sort buffer are using K, M, G, T unit
# The sort buffer should be allocated per thread.
# So, the max size of the sort buffer is sort_buffer_size * max_clients.
sort_buffer_size=2M

# The maximum number of concurrent client connections the server will accept.
# This value also means the total # of concurrent transactions.
max_clients=100

# TCP port id for the CUBRID programs (used by all clients).
cubrid_port_id=1523

[service]

service=server,broker,manager

[common]

data_buffer_size=512M
log_buffer_size=256M
sort_buffer_size=2M
max_clients=100

# TCP port id for the CUBRID programs (used by all clients).
cubrid_port_id=1523

[@testdb]
data_buffer_size=128M
max_clients=10

Connection-Related Parameters¶

The following are parameters related to the database server. The type and value range for each parameter are as follows:

cubrid_port_id

cubrid_port_id is a parameter to configure the port to be used by the master process. The default value is 1,523. If the port 1,523 is already being used on the server where CUBRID is installed or it is blocked by a firewall, an error message, which means the master server is not connected because the master process cannot be running properly, is displayed. If such port conflict occurs, the administrator must change the value of cubrid_port_id considering the server environment.

check_peer_alive

check_peer_alive is a parameter to decide whether you execute the function checking that the client/server processes work well. The default is both.

The client processes connecting with a server process are the broker application server(cub_cas) process, the process copying replication logs(copylogdb), the process applying replication logs. (applylogdb), CSQL interpreter(csql), etc. The server process and the client process which connected with it wait each other's response. But if one of them cannot get the data for a long time(example: exceeding 5 sec), it will check or not if the other works well based on the configuration of check_peer_alive parameter. During this processes, if it is judged that the process doesn't work properly, it disconnect forcibly.

The values and the working methods are as follows.

• both: As the server process accesses to the client process by ECHO(7) port, it checks if the client process works well. The client process also does the same thing to the server process(The default value).
• server_only: Only the server process checks whether the client process works well.
• client_only: Only the client process checks whether the server process works well.
• none: None of the server and client processes check whether the other process works well.

Specially, if ECHO(7) port is blocked by the firewall configuration, each process can mistake that the other process was exited. Therefore, you should avoid this problem by setting this parameter's value as none.

db_hosts

db_hosts is a parameter to configure a list of the database server hosts to which clients can connect, and the connection order. The server host list consists of multiple server host names, and host names are separated by spaces or colons (:). Duplicate or non-existent names are ignored.

The following example shows the values of the db_hosts parameter. In this example, connections are attempted in the order of host1 > host2 > host3.

db_hosts="hosts1:hosts2:hosts3"

To connect to the server, the client first tries to connect to the specified server host referring to the database location file (databases.txt). If the connection fails, the client then tries to connect to the first one of the secondarily specified server hosts by referring to the value of the db_hosts parameter in the database configuration file (cubrid.conf).

max_clients

max_clients is a parameter to configure the maximum number of clients (usually broker application processes (CAS)) which allow concurrent connections to the database server. The max_clients parameter refers to the number of concurrent transactions per database server process. The default value is 100.

To guarantee performance while increasing the number of concurrent users in CUBRID environment, you need to make the appropriate value of the max_clients (cubrid.conf) parameter and the MAX_NUM_APPL_SERVER (cubrid_broker.conf) parameter. That is, you are required to configure the number of concurrent connections allowed by databases with the max_clients parameter. You should also configure the number of concurrent connections allowed by brokers with the MAX_NUM_APPL_SERVER parameter.

For example, in the cubrid_broker.conf file, two node of a broker where the MAX_NUM_APPL_SERVER value of [%query_editor] is 50 and the MAX_NUM_APPL_SERVER value of [%BROKER1] is 50 is trying to connect one database server, the concurrent connections (max_clients value) allowed by the database server can be configured as follows:

• (the maximum number of 100 by each node of a broker) * (two node of a broker) + (10 spare for database server connections of internal CUBRID process such as database server connection of CSQL Interpreter or HA log replication process) = 210

Especially, in HA environment, the value must be greater than the sum specified in MAX_NUM_APPL_SERVER of every broker node which connects to the same database.

Note that the memory usage is affected by the value specified in max_clients. That is, if the number of value is high, the memory usage will increase regardless of whether or not the clients actually access the database.

Note

In Linux system, max_clients parameter is related to "ulimit -n" command, which specifies the maximum number of file descriptors which a process can use. File descriptor includes not only a file, but also a network socket. Therefore, the number of "ulimit -n" should be greater than the number of max_clients.

tcp_keepalive

tcp_keepalive is a parameter which specifies if you apply SO_KEEPALIVE option to TCP network protocol or not. The default is yes. If this value is no, DB server-side connection can be disconnected when transaction logs are not copied for a long time in the firewall environment between master and slave.

Memory-Related Parameters¶

The following are parameters related to the memory used by the database server or client. The type and value range for each parameter are as follows:

data_buffer_size

data_buffer_size is a parameter to configure the size of data buffer to be cached in the memory by the database server. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 32,768 * db_page_size (512M when db_page_size is 16K), and the minimum value is 1,024 * db_page_size (16M when db_page_size is 16K). The maximum value in 64-bit CUBRID is INT_MAX * db_page_size. Note that the maximum value in 32-bit CUBRID is 2G.

The greater the value of the data_buffer_size parameter, the more data pages to be cached in the buffer, thus providing the advantage of decreased disk I/O cost. However, if this parameter is too large, the buffer pool can be swapped out by the operating system because the system memory is excessively occupied. It is recommended to configure the data_buffer_size parameter in a way the required memory size is less than two-thirds of the system memory size.

• Required memory size = data buffer size (data_buffer_size)

index_scan_oid_buffer_size

index_scan_oid_buffer_size is a parameter to configure the size of buffer where the OID list is to be temporarily stored during the index scan. You can set unit K, which stands for KB (kilobytes). If you omit the unit, bytes will be applied. The default value is 4 * db_page_size (64K when db_page_size is 16K), and the minimum value is 0.05 * db_page_size (about 1K when db_page_size is 16K).

The size of the OID buffer tends to vary in proportion to the value of the index_scan_oid_buffer_size parameter and the page size set when the database was created. In addition, the bigger the size of such OID buffer, the more the index scan cost. You can set the value of the index_scan_oid_buffer_size by considering these factors.

max_agg_hash_size

max_agg_hash_size is a parameter to configure the maximum memory per transaction allocated for hashing the tuple groups in a query containing aggregation. The default is 2,097,152(2M), the minimum size is 32,768(32K), and the maximum size is 134,217,728(128MB).

If NO_HASH_AGGREGATE hint is specified, hash aggregate evaluation will not be used. As a reference, see agg_hash_respect_order.

sort_buffer_size

sort_buffer_size is a parameter to configure the size of buffer to be used when a query is processing sorting. The server assigns one sort buffer for each client's sorting-request, and releases the assigned buffer memory when sorting is complete. A sorting query includes not only SELECT sorting query, but also index-creating query.

You can set a unit as B, K, M, G or T, which stand for bytes, kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The default value is 128 * db_page_size (2M when db_page_size is 16K), and the minimum value is 1 * db_page_size (16K when db_page_size is 16K).

temp_file_memory_size_in_pages

temp_file_memory_size_in_pages is a parameter to configure the number of buffer pages to cache temporary result of a query. The default value is 4 and the maximum value is 20.

• Required memory size = the number of temporary memory buffer pages (temp_file_memory_size_in_pages * page size)
• The number of temporary memory buffer pages = the value of the temp_file_memory_size_in_pages parameter
• Page size = the value of the page size specified by the -s option of the cubrid createdb utility during the database creation

The spaces to store the temporary result are as follows.

• Cache buffer to store the temporary result (acquired by temp_file_memory_size_in_pages parameter)
• Permanent volumes with the purpose of storing temporary data.
• Temporary volumes

If the previous space is exhausted, then the next space is used as the following order: Cache buffer for storing temporary result -> Permanent volumes -> Temporary volumes.

thread_stacksize

thread_stacksize is a parameter to configure the stack size of a thread. The default value is 1048576 bytes. The value of the thread_stacksize parameter must not exceed the stack size allowed by the operating system.

Disk-Related Parameters¶

The following are disk-related parameters for defining database volumes and storing files. The type and value range for each parameter are as follows:

db_volume_size

db_volume_size is a parameter to configure the following values. You can set a unit as B, K, M, G or T, which stand for bytes, kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The default value is 512M.

• The default database volume size when cubrid createdb and cubrid addvoldb utility is used without --db-volume-size option.
• The default size of volume that is added automatically when database is full.

dont_reuse_heap_file

dont_reuse_heap_file is a parameter to configure whether or not heap files, which are deleted when deleting the table (DROP TABLE), are to be reused when creating a new table (CREATE TABLE). If this parameter is set to no, the deleted heap files can be reused; if it is set to yes, the deleted heap files are not used when creating a new table. The default value is no.

log_volume_size

log_volume_size is a parameter to configure the default size of log volume file when the cubrid createdb utility is used without --log-volume-size option. You can set a unit as B, K, M, G or T, which stand for bytes, kilobytes (KB), megabytes (MB), gigabytes (GB) and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The default value is 512M.

temp_file_max_size_in_pages

temp_file_max_size_in_pages is a parameter to configure the maximum number of pages to which temporary volumes can be extended. By default, this value is -1, which means that temporary volumes can occupy an unlimited disk space. A positive value will set a limit to these values (exceeding it may show an error and cancel some big queries).

If the parameter is configured to 0, temporary volumes are not created automatically; the administrator must create permanent volumes with the purpose of storing temporary data by using the cubrid addvoldb utility.

For more details see Temporary Volume

temp_volume_path

temp_volume_path is a parameter to configure the directory in which to create temporary volumes used for the execution of complex queries or sorting. The default value is the volume location configured during the database creation.

unfill_factor

unfill_factor is a parameter to configure the rate of disk space to be allocated in a heap page for data updates. The default value is 0.1. That is, the rate of free space is configured to 10%. In principle, data in the table is inserted in physical order. However, if the size of the data increases due to updates and there is not enough space for storage in the given page, performance may degrade because updated data must be relocated to another page. To prevent such a problem, you can configure the rate of space for a heap page by using the unfill_factor parameter. The allowable maximum value is 0.3 (30%). In a database where data updates rarely occur, you can configure this parameter to 0.0 so that space will not be allocated in a heap page for data updates. If the value of the unfill_factor parameter is negative or greater than the maximum value, the default value (0.1) is used.

volume_extension_path

volume_extension_path is a parameter to configure the directory where automatically extended volumes are to be created. The default value is the volume location configured during the database creation.

Error Message-Related Parameters¶

The following are parameters related to processing error messages recorded by CUBRID. The type and value range for each parameter are as follows:

call_stack_dump_activation_list

call_stack_dump_activation_list is a parameter to configure a certain error number for which a call stack is to be dumped to a server error log (located in $CUBRID/log/server directory) as an exception even when you configure that a call stack will not be dumped for any errors. Therefore, the call_stack_dump_activation_list parameter is effective only when call_stack_dump_on_error=no.

If this value is not configured, the default value is "DEFAULT" keyword. This keyword includes below errors. "DEFAULT" keyword can be used together with other error numbers.

Error Number	Error Message
-2	Internal system failure: no more specific information is available.
-7	Trying to format disk volume xxx with an incorrect value xxx for number of pages.
-13	An I/O error occurred while reading page xxx of volume xxx.
-14	An I/O error occurred while writing page xxx of volume xxx.
-17	Internal error: fetching deallocated pageid xxx of volume xxx.
-19	Internal error: pageptr = xxx of page xxx of volume xxx is not fixed.
-21	Internal error: unknown sector xxx of volume xxx.
-22	Internal error: unknown page xxx of volume xxx.
-45	Slot xxx on page xxx of volume xxx is allocated to an anchored record. A new record cannot be inserted here.
-46	Internal error: slot xxx on page xxx of volume xxx is not allocated.
-48	Accessing deleted object xxx|xxx|xxx.
-50	Internal error: relocation record of object xxx|xxx|xxx may be corrupted.
-51	Internal error: object xxx|xxx|xxx may be corrupted.
-52	Internal error: object overflow address xxx|xxx|xxx may be corrupted.
-76	Your transaction (index xxx, xxx@xxx|xxx) timed out waiting on xxx on page xxx|xxx. You are waiting for user(s) xxx to release the page lock.
-78	Internal error: an I/O error occurred while reading logical log page xxx (physical page xxx) of xxx.
-79	Internal error: an I/O error occurred while writing logical log page xxx (physical page xxx) of xxx.
-81	Internal error: logical log page xxx may be corrupted.
-90	Redo logging is always a page level logging operation. A data page pointer must be given as part of the address.
-96	Media recovery may be needed on volume xxx.
-97	Internal error: unable to find log page xxx in log archives.
-313	Object buffer underflow while reading.
-314	Object buffer overflow while writing.
-407	Unknown key xxx referenced in B+tree index {vfid: (xxx, xxx), rt_pgid: xxx, key_type: xxx}.
-414	Unknown class identifier: xxx|xxx|xxx.
-415	Invalid class identifier: xxx|xxx|xxx.
-416	Unknown representation identifier: xxx.
-417	Invalid representation identifier: xxx.
-583	Trying to allocate an invalid number (xxx) of pages.
-603	Internal Error: Sector/page table of file VFID xxx|xxx seems corrupted.
-836	LATCH ON PAGE(xxx|xxx) TIMEDOUT
-859	LATCH ON PAGE(xxx|xxx) ABORTED
-890	Partition failed.
-891	Appropriate partition does not exist.
-976	Internal error: Table size overflow (allocated size: xxx, accessed size: xxx) at file table page xxx|xxx(volume xxx)
-1040	HA generic: xxx.
-1075	Descending index scan aborted because of lower priority on B+tree with index identifier: (vfid = (xxx, xxx), rt_pgid: xxx).

The following example shows how to make error numbers only -115 and -116, perform call-stack dump.

call_stack_dump_on_error= no
call_stack_dump_activation_list=-115,-116

The following example shows how to make error numbers -115, -116 and "DEFAULT" error numbers, perform call-stack dump.

call_stack_dump_on_error= no
call_stack_dump_activation_list=-115,-116, DEFAULT

call_stack_dump_deactivation_list

call_stack_dump_deactivation_list is a parameter to configure a certain error number for which a call stack is not to be dumped when you configure that a call stack will be dumped for any errors. Therefore, the call_stack_dump_deactivation_list parameter is effective only when call_stack_dump_on_error is set to yes.

The following example shows how to configure the parameter so that call stacks will be dumped for any errors, except the ones whose numbers are -115 and -116.

call_stack_dump_on_error= yes
call_stack_dump_deactivation_list=-115,-116

call_stack_dump_on_error

call_stack_dump_on_error is a parameter to configure whether or not to dump a call stack when an error occurs in the database server. If this parameter is set to no, a call stack for any errors is not dumped. If it is set to yes, a call stack for all errors is dumped. The default value is no.

error_log

error_log is a server/client parameter to configure the name of the error log file when an error occurs in the database server. The name of the error log file must be in the form of <database_name>_<date>_<time>.err. However, the naming rule of the error log file does not apply to errors for which the system cannot find the database server information. Therefore, error logs are recorded in the cubrid.err file. The error log file cubrid.err is stored in the $CUBRID/log/server directory.

error_log_level

error_log_level is a server parameter to configure an error message to be stored based on severity. There are five different levels which range from WARNING (lowest level), to FATAL (highest level). The inclusion relation in messages is FATAL < ERROR < SYNTAX < NOTIFICATION < WARNING. The default is NOTIFICATION. If severity of error is NOTIFICATION, error messages with NOTIFICATION, SYNTAX, ERROR and FATAL levels are written to the log file.

error_log_warning

error_log_warning is a parameter to configure whether or not error messages with a severity level of WARNING are to be displayed. Its default value is no. For this reason, you must set error_log_warning to yes to store WARNING messages to an error log file.

error_log_size

error_log_size is a parameter to configure the maximum number of lines per an error log file. The default value is 512M. If it reaches up to the specified number, the <database_name>_<date>_<time>.err.bak file is created.

Concurrency/Lock-Related Parameters¶

The following are parameters related to concurrency control and locks of the database server. The type and value range for each parameter are as follows:

deadlock_detection_interval_in_secs

deadlock_detection_interval_in_secs is a parameter to configure the interval (in seconds) in which deadlocks are detected for stopped transactions. If a deadlock occurs, CUBRID resolves the problem by rolling back one of the transactions. The default value is 1 second and the minimum value is 0.1 second. This value is rounded up by 0.1 sec. unit. For example, if an input value is 0.12 seconds, the value is rounded up to 0.2 seconds. Note that deadlocks cannot be detected if the detection interval is too long.

isolation_level

isolation_level is a parameter to configure the isolation level of a transaction. The higher the isolation level, the less concurrency and the less interruption by other concurrent transactions. The isolation_level parameter can be configured to an integer value from 4 to 6, which represent isolation levels, or character strings. The default value is READ COMMITTED. For details about each isolation level and parameter values, see Transaction Isolation Level and the following table.

Isolation Level	isolation_level Parameter Value
SERIALIZABLE	"TRAN_SERIALIZABLE" or 6
REPEATABLE READ	"TRAN_REP_CLASS_REP_INSTANCE" or "TRAN_REP_READ" or 5
READ COMMITTED	"TRAN_REP_CLASS_COMMIT_INSTANCE" or "TRAN_READ_COMMITTED" or "TRAN_CURSOR_STABILITY" or 4

• TRAN_SERIALIZABLE : This isolation level ensures the highest level of consistency. For details, see SERIALIZABLE Isolation Level.
• TRAN_REP_READ : This isolation level can incur phantom read. For details, see REPEATABLE READ Isolation Level.
• TRAN_READ_COMMITTED : This isolation level can incur unrepeatable read. For details, see READ COMMITTED Isolation Level.

Note

9.3 or less version supports the below levels additionally. From 10.0, concurrency can be guaranteed more because MVCC method is applied when multiple concurrent transactions are processed; therefore, the below isolation levels are not used anymore.

• TRAN_REP_CLASS_UNCOMMIT_INSTANCE : This isolation level can incur dirty read.
• TRAN_COMMIT_CLASS_COMMIT_INSTANCE : This isolation level can incur unrepeatable read. It allows modification of table schema by current transactions while data is being retrieved.
• TRAN_COMMIT_CLASS_UNCOMMIT_INSTANCE : This isolation level can incur dirty read. It allows modification of table schema by current transactions while data is being retrieved.

lock_escalation

lock_escalation is a parameter to configure the maximum number of locks permitted before row level locking is extended to table level locking. The default value is 100,000. If the value of the lock_escalation parameter is small, the overhead by memory lock management is small as well; however, the concurrency decreases. On the other hand, if the configured value is large, the overhead is large as well; however, the concurrency increases.

lock_timeout

lock_timeout is a client parameter to configure the lock waiting time. If the lock is not permitted within the specified time period, the given transaction is canceled, and an error message is returned. If the parameter is configured to -1, which is the default value, the waiting time is infinite until the lock is permitted. If it is configured to 0, there is no waiting for locks.

You can set a unit as s, min or h, which stands for seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied, and it is rounded up to seconds. For example, 1ms will be 1s, and 1001ms will be 2s.

rollback_on_lock_escalation

It specifies rolling back the transaction or not when the lock escalation occurs. The default is no.

If this parameter is specified with yes, the error log is written without lock escalation on the lock-escalating time and this lock escalation request is failed with rolling back the transaction. If it is specified with no, the lock escalation is performed and the transaction is continued.

When the lock escalation occurs, record locks are transformed into a table lock and lock-releasing time can take long, so other transaction's access to the table can be impossible. However, if you specify the value of lock_escalation parameter(it specifies the number of record locks occurring the lock escalation) bigger, the system can overuse the memory resource.

Logging-Related Parameters¶

The following are parameters related to logs used for database backup and restore. The types and value range for each parameter are as follows:

adaptive_flush_control

adaptive_flush_control is a parameter used automatically to adjust the flush capacity at every 50 ms depending on the current status of the flushing operation. The default value is yes. That is, this capacity is increased if a large number of INSERT or UPDATE operations are concentrated at a certain point of time and the number of flushed pages reaches the max_flush_size_per_second parameter value; and is decreased otherwise. In the same way, you can distribute the I/O load by adjusting the flush capacity on a regular basis depending on the workload.

background_archiving

background_archiving is a parameter used to create temporary archive logs periodically at a specific time. It is useful when balancing disk I/O load which has been caused by archiving logs. The default is yes.

checkpoint_every_size

checkpoint_every_size is a parameter to configure checkpoint interval by log page. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 10,000 * log_page_size (156.25M when log_page_size is 16K).

You can distribute disk I/O overload at the checkpoint by specifying lower size in the checkpoint_every_size parameter, especially in the environment where INSERT / UPDATE are heavily loaded at a specific time.

Checkpoint is a job to record every modified page in data buffers to database volumes (disk) at a specific point. Checkpoint can shrink a restore time after the database failure because this makes the transaction logs which have been generated previously than the checkpoint time needless when the restore is processed. However, an efficient checkpoint interval should be properly considered because this job can occur a lot of disk I/O.

Note

There are three ways to run checkpoint in CUBRID. The following two ways are provided by the setting of cubrid.conf.

• checkpoint_interval: After the previous checkpoint is done, checkpoint is periodically processed at every time after the term of this parameter value.
• checkpoint_every_size: Checkpoint is periodically processed at every time after the transaction log size of this parameter value.

If one condition on the above parameters is satisfied, checkpoint is processed.

The following two ways are provided by a user's command.

• If you run ";checkpoint" command in the CSQL interpreter, which is run with a "DBA" user, checkpoint is processed.

As a reference, if you run backup command during checkpoint, backup command is blocked until checkpoint is ended.

checkpoint_interval

checkpoint_interval is a parameter to configure execution period of checkpoint. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied, and it is rounded up to seconds. For example, 1ms will be 1s, and 1001ms will be 2s. The default value is 6min and the minimum value is 1min.

checkpoint_sleep_msecs

checkpoint_sleep_msecs is a parameter to let the job which flushes a buffer's data into a disk process slowly. The default is 1 (millisecond).

force_remove_log_archives

force_remove_log_archives is a parameter to configure whether to allow the deletion of the files other than the recent log archive files whose number is specified by log_max_archives. The default value is yes.

If the value is set to yes, the files will be deleted other than the recent log archive files for which the number is specified by log_max_archives.

If it is set to no, the log archive files will not be deleted. Exceptionally, if ha_mode is set to on, the files other than the log archive files required for the HA-related processes and the recent log archive files of which the number is specified by log_max_archives will be deleted.

For setting up the CUBRID HA environment, see Environment Configuration.

log_buffer_size

log_buffer_size is a parameter to configure the size of log buffer to be cached in the memory. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 16k * log_page_size (256M when log_page_size is 16K).

If the value of the log_buffer_size parameter is large, performance can be improved (due to the decrease in disk I/O) in an environment where transactions are long and numerous. Moreover, CUBRID Multiversion Concurrency Control system relies on log to access previous row versions and to vacuum invisible versions from database. It is recommended to configure an appropriate value considering the memory size and operations of the system where CUBRID is installed.

• Required memory size = the size of log buffer (log_buffer_size)

log_max_archives

log_max_archives is a parameter to configure the maximum number of archive log files. The minimum value is 0 and default value is INT_MAX (2,147,483,647). Its operations can differ depending on the configuration of force_remove_log_archives. For example, when log_max_archives is 3 and force_remove_log_archives is yes in the cubrid.conf file, the most recent three archive log files are recorded and when a fourth archiving log file is generated, the oldest archive log file is automatically deleted; the information about the deleted archive logs are recorded in the *_lginf file.

However, if an active transaction still refers to an existing archive log file, the archive log file will not be deleted. That is, if a transaction starts at the point that the first archive log file is generated, and it is still active until the fifth archive log is generated, the first archive log file cannot be deleted.

Also, if the information of archive logs is not applied to database volumes, these are not deleted. (Archive logs after which a checkpoint has occurred keep the information of modified pages of a data buffer; therefore, they are required to restore a database.)

If you change the value of log_max_archives dynamically during database operation, changed value will be applied when a new log archive file is created. For example, if you change this value from 10 to 5, old 5 files will be deleted when a new log archive file is created.

For setting up the CUBRID HA environment, see Environment Configuration.

Note

In 2008 R4.3 or lower and in 9.1, log_max_archives was also used for specifying the maximum number of keeping replication log files in HA environment. From 2008 R4.4 and 9.2, ha_copy_log_max_archives of cubrid_ha.conf is in charge of this role.

log_trace_flush_time

When the log flushing time takes more time than the time you set in this parameter, this event is recorded in the log of the database server.

The example of written information is as below.

03/18/14 10:20:45.889 - LOG_FLUSH_THREAD_WAIT
  total flush count: 1 page(s)
  total flush time: 310 ms
  time waiting for log writer: 308 ms
  last log writer info
    client: DBA@cdbs037.cub|copylogdb(15312)
    time spent by log writer: 308 ms

• LOG_FLUSH_THREAD_WAIT: Event name
• total flush count: The number of flushed pages when the event occurs
• total flush time: Total spent time when the log is flushed
• time waiting for log writer: The time LFT(Log Flushing Thread) has been waiting for LWT(Log Writer Thread)
• last log writer info
  • DBA@cdbs037.cub|copylogdb(15312): copylogdb information related to LWT which let LFT wait <user@host_name|client_name(pid)>
  • time spent by log writer: Time spend by LWT measured in LWT; in general, this value is the same as the value of "time waiting for log writer")

max_flush_size_per_second

max_flush_size_per_second is a parameter to configure the maximum flush capacity when the flushing operation is performed from a buffer to a disk. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 10,000 * db_page_size (156.25M when db_page_size is 16K). That is, you can prevent concentration of I/O load at a certain point of time by configuring this parameter to control the maximum flush capacity per second.

If a large number of INSERT or UPDATE operations are concentrated at a certain point of time, and the flush capacity reaches the maximum capacity set by this parameter, only log pages are flushed to the disk, and data pages are no longer flushed. Therefore, you must set an appropriate value for this parameter considering the workload of the service environment.

remove_log_archive_interval_in_secs

Archive logs which exceed the specified number in the log_max_archives are removed when checkpoint occurs. By the way, many archive logs can be removed at once frequently after being piled up when jobs such as data migration or big data batch processing are performed. If files are removed at once like these, I/O overhead of database server is rapidly risen; therefore, we need to decrease this burden.

remove_log_archive_interval_in_secs parameter lets archive logs delete slowly to shrink this burden. The default is 0 (second). In the situations which jobs like big data batch processing occur frequently, it is recommended to set the deletion interval as a 60 seconds if the disk space is enough.

sync_on_flush_size

sync_on_flush_size is a parameter to configure the interval in pages between after data and log pages are flushed from buffer and before they are synchronized with FILE I/O of operating system. The default value is 200 * db_page_size (3.125M when db_page_size is 16K). That is, the CUBRID Server performs synchronization with the FILE I/O of the operating system whenever 200 pages have been flushed. This is also a parameter related to I/O load.

Transaction Processing-Related Parameters¶

The following are parameters for improving transaction commit performance. The type and value range for each parameter are as follows:

async_commit

async_commit is a parameter used to activate the asynchronous commit functionality. If the parameter is set to no, which is the default value, the asynchronous commit is not performed; if it is set to yes, the asynchronous commit is executed. The asynchronous commit is a functionality that improves commit performance by completing the commit for the client before commit logs are flushed on the disk and having the log flush thread (LFT) perform log flushing in the background. Note that already committed transactions cannot be restored if a failure occurs on the database server before log flushing is performed.

group_commit_interval_in_msecs

group_commit_interval_in_msecs is a parameter to configure the interval (in milliseconds), at which the group commit is to be performed. If the parameter is configured to 0, which is the default value, the group commit is not performed. The group commit is a functionality that improves commit performance by combining multiple commits that occurred in the specified time period into a group so that commit logs are flushed on the disk at once.

Statement/Type-Related Parameters¶

The following are parameters related to SQL statements and data types supported by CUBRID. The type and value range for each parameter are as follows:

add_column_update_hard_default

add_column_update_hard_default is a parameter to configure whether or not to provide the hard default value as the input value for a column when you add a new column to the ALTER TABLE ... ADD COLUMN clause.

When there is NOT NULL constraint and no DEFAULT constraint, if a value of this parameter is set to yes, the value of newly added column will be inserted as hard default value; if it is set to no, CUBRID returns an error. For the hard default for each type, see the CHANGE/MODIFY Clauses of the ALTER TABLE statement.

SET SYSTEM PARAMETERS 'add_column_update_hard_default=yes';

CREATE TABLE tbl (i int);
INSERT INTO tbl VALUES (1),(2);
ALTER TABLE tbl ADD COLUMN j INT NOT NULL;

SELECT * FROM tbl;

            i          j
=========================
            1          0
            2          0

SET SYSTEM PARAMETERS 'add_column_update_hard_default=no';

CREATE TABLE tbl (i INT);
INSERT INTO tbl VALUES (1),(2);
ALTER TABLE tbl ADD COLUMN j INT NOT NULL;

ERROR: Cannot add NOT NULL constraint for attribute "j": there are existing NULL values for this attribute.

alter_table_change_type_strict

alter_table_change_type_strict is a parameter to configure whether or not to allow the conversion of column values according to the type change, and the default value is no. If a value for this parameter is set to no, the value may be changed when you change the column types or when you add NOT NULL constraints; if it is set to yes, the value is not changed. For details, see CHANGE Clause in the CHANGE/MODIFY Clauses.

ansi_quotes

ansi_quotes is a parameter used to enclose symbols and character string to handle identifiers. The default value is yes. If this parameter value is set to yes, double quotations are handled as identifier symbols and single quotations are handled as character string symbols. If it is set to no, both double and single quotations are handled as character string symbols.

block_ddl_statement

block_ddl_statement is a parameter used to limit the execution of DDL (Data Definition Language) statements by the client. If the parameter is set to no, the given client is allowed to execute DDL statements. If it is set to yes, the client is not permitted to execute DDL statements. The default value is no.

block_nowhere_statement

block_nowhere_statement is a parameter used to limit the execution of UPDATE / DELETE statements without a condition clause (WHERE) by the client. If the parameter is set to no, the given client is allowed to execute UPDATE / DELETE statements without a condition clause. If it is set to yes, the client is not permitted to execute UPDATE / DELETE statements without a condition clause. The default value is no.

compat_numeric_division_scale

compat_numeric_division_scale is a parameter to configure the scale to be displayed in the result (quotient) of a division operation. If the parameter is set to no, the scale of the quotient is 9, if it is set to yes, the scale is determined by that of the operand. The default value is no.

cte_max_recursions

cte_max_recursions is a parameter to limit the maximum number of iterations when executing the recursive part of the CTE (Common Table Expressions) statement. This avoids infinite loop and potential issues produced by the size of temporary lists.

default_week_format

default_week_format is a parameter to configure default value for the mode attribute of the WEEK() function. The default value is 0. For details, see WEEK().

intl_check_input_string

intl_check_input_string is a parameter to determine whether or not to check that string entered is correctly corresponded to character set used. The default value is no. If this value is no and character set is UTF-8 and incorrect data is enter which violate UTF-8 byte sequence, it can show abnormal behavior or database server and applications can be terminated abnormally. However, if it is guaranteed this problem does not happen, it has advantage in performance not to do it.

UTF-8 and EUC-KR can be checked; ISO-8859-1 is one-byte encoding so it does not have to be checked because every byte is valid.

group_concat_max_len

group_concat_max_len is a parameter used to limit the return value size of the GROUP_CONCAT() function. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 1,024. The minimum value is 4 and the maximum value is 33,554,432 bytes.

This function is affected by string_max_size_bytes parameter; if the value of group_concat_max_len is greater than the value string_max_size_bytes and the result size of GROUP_CONCAT exceeds the value of string_max_size_bytes, an error occurs.

intl_check_input_string

intl_check_input_string is a parameter to determine whether or not to check that string entered is correctly corresponded to character set used. The default value is no. If this value is no and character set is UTF-8 and incorrect data is enter which violate UTF-8 byte sequence, it can show abnormal behavior or database server and applications can be terminated abnormally. However, if it is guaranteed this problem does not happen, it has advantage in performance not to do it.

UTF-8 and EUC-KR can be checked; ISO-8859-1 is one-byte encoding so it does not have to be checked because every byte is valid.

intl_collation

intl_collation is a parameter which specifies a collation name about a specific application client. Specifying this parameter is the same as changing a collation of application client by using "SET NAMES" statement. Specifying a collation includes a charset.

The following two statements behave the same.

SET NAMES utf8;
SET SYSTEM PARAMETERS 'intl_collation=utf8_bin';

For an available value of intl_collation, see Collation.

intl_date_lang

intl_date_lang is a parameter used to input/output the values of TIME, DATE, DATETIME, and TIMESTAMP. If language name is omitted, it specifies a locale format of string of localized calendar (month, weekday, and AM/PM).

The values allowed are as follows: Note that to use all values, locale library should be configured except built-in locale. For configuring locale, see Locale Setting.

Language	Locale Name of Language
English	en_US
German	de_DE
Spanish	es_ES
French	fr_FR
Italian	it_IT
Japanese	ja_JP
Cambodian	km_KH
Korean	ko_KR
Turkish	tr_TR
Vietnamese	vi_VN
Chinese	zh_CN
Romanian	ro_RO

The function recognizing input string based on calendar format of specified language is as follows:

• TO_DATE()
• TO_TIME()
• TO_DATETIME()
• TO_TIMESTAMP()
• STR_TO_DATE()

The function outputting string based on calendar format of specified language is as follows:

• TO_CHAR()
• DATE_FORMAT()
• TIME_FORMAT()

intl_number_lang

intl_number_lang is a parameter used to specify locale applied when numeric format is assigned to input/output string in the function where a string is converted to number or number is converted to string. A delimiter and decimal symbol are used for numeric localization. In general, a comma and period are used; however, it can be changeable based on locale. For example, while number 1000.12 is written as 1,000.12 in most locale, it is written as 1.000,12 in tr_TR locale.

The function recognizing input string based on calendar format of specified language is as follows:

• TO_NUMBER()

The function outputting string based on calendar format of specified language is as follows:

• FORMAT()
• TO_CHAR()

no_backslash_escapes

no_backslash_escapes is a parameter to configure whether or not to use backslash (\) as an escape character, and the default value is yes. If a value for this parameter is set to no, backslash (\) will be used as an escape character; if it is set to yes, backslash (\) will be used as a normal character. For example, if this value is set to no, "\n" means a newline character. For details, see Escape Special Characters.

only_full_group_by

only_full_group_by is a parameter to configure whether or not to use extended syntax about using GROUP BY statement.

If this parameter value is set to no, an extended syntax is applied thus, a column that is not specified in the GROUP BY statement can be specified in the SELECT column list. If it is set to yes, a column that is only specified in the GROUP BY statement can be the SELECT column list.

The default value is no. Therefore, specify the only_full_group_by parameter value to yes to execute queries by SQL standards. Because the extended syntax is not applied in this case, an error below is displayed.

ERROR: Attributes exposed in aggregate queries must also appear in the group by clause.

oracle_style_empty_string

oracle_style_empty_string is a parameter used to improve compatibility with other DBMS (Database Management Systems); it specifies to process empty string and NULL as the same value. The default is no. If the oracle_style_empty_string parameter is set to no, the character string is processed as a valid string; if it is set to yes, according to each function, the empty string is processed as NULL or NULL is processed as the empty string.

Note

Other functions except below functions are not affected by oracle_style_empty_string parameter.

• Functions processing an empty string and NULL into NULL when oracle_style_empty_string=yes.
  • ASCII()
  • CONCAT_WS()
  • ELT()
  • FIELD()
  • FIND_IN_SET()
  • FROM_BASE64()
  • INSERT()
  • INSTR()
  • LOWER()
  • LEFT()
  • LOCATE()
  • LPAD()
  • LTRIM()
  • MID()
  • POSITION()
  • REPEAT()
  • REVERSE()
  • RIGHT()
  • RPAD()
  • RTRIM()
  • SPACE()
  • STRCMP()
  • SUBSTR()
  • SUBSTRING()
  • SUBSTRING_INDEX()
  • TO_BASE64()
  • TRANSLATE()
  • TRIM()
  • UPPER()
• Functions processing an empty string and NULL into an empty string when oracle_style_empty_string=yes.
  • CONCAT()
  • REPLACE()

Note

REPLACE() function has the different behavior in the previous versions of 10.0 when oracle_style_empty_string=yes.

SELECT REPLACE ('abc', 'a', '');

In the above query, the version of 10.0 or more return 'bc' because it processes the input of an empty string as an empty string; the previous version of 10.0 returns NULL because it processes the input of an empty string as NULL.

pipes_as_concat

pipes_as_concat is a parameter to configure how to handle a double pipe symbol. The default value is yes. If this parameter value is set to yes, a double pipe symbol is handled as a concatenation operator if no, it is handled as the OR operator.

plus_as_concat

plus_as_concat is a parameter to configure the plus (+) operator, and the default value is yes. If a value for this parameter is set to yes, the plus (+) operator will be interpreted as a concatenation operator; if it is set to no, the operator will be interpreted as a numeric operator.

-- plus_as_concat = yes
SELECT '1'+'1';

         '1'+'1'
======================
         '11'

SELECT '1'+'a';

         '1'+'a'
======================
         '1a'

-- plus_as_concat = no
SELECT '1'+'1';

                '1'+'1'
==========================
 2.000000000000000e+000

SELECT '1'+'a';

ERROR: Cannot coerce 'a' to type double.

require_like_escape_character

require_like_escape_character is parameter to configure whether or not to use an ESCAPE character in the LIKE clause, and the default value is no. If a value for this parameter is set to yes and a value for no_backslash_escapes is set to no, backslash (\) will be used as an ESCAPE character in the strings of the LIKE clause, otherwise you should specify an ESCAPE character by using the LIKE ... ESCAPE clause. For details, see LIKE.

return_null_on_function_errors

return_null_on_function_errors is a parameter used to define actions when errors occur in some SQL functions, and the default value is no. If a value for this parameter is set to yes, NULL is returned; if it is set to no, an error is returned when the error occurs in functions, and the related message is displayed.

The following SQL functions are affected by this system parameter.

Date/Time functions

• ADDDATE()
• ADDTIME()
• DATEDIFF()
• DAY()
• DAYOFMONTH()
• DAYOFWEEK()
• DAYOFYEAR()
• FROM_DAYS()
• FROM_UNIXTIME()
• HOUR()
• LAST_DAY()
• MAKEDATE()
• MAKETIME()
• MINUTE()
• MONTH()
• QUARTER()
• SEC_TO_TIME()
• SECOND()
• TIME()
• TIME_TO_SEC()
• TIMEDIFF()
• TO_DAYS()
• WEEK()
• WEEKDAY()
• YEAR()

String functions

• ASCII()
• BIN()
• BIT_LENGTH()
• CHR()

Numeric functions

• ABS()
• ACOS()
• ASIN()
• ATAN()
• ATAN2()
• CEIL()
• CONV()
• COS()
• COT()
• DEGREES()
• EXP()
• FLOOR()
• LN()
• LOG2()
• LOG10()
• MOD()
• POW()
• RADIANS()
• SIGN()
• SIN()
• SQRT()
• TAN()
• TRUNC()
• WIDTH_BUCKET()

SET SYSTEM PARAMETERS 'return_null_on_function_errors=no';
SELECT YEAR('12:34:56');

ERROR: Conversion error in time format.

SET SYSTEM PARAMETERS 'return_null_on_function_errors=yes';
SELECT YEAR('12:34:56');

   year('12:34:56')
======================
   NULL

string_max_size_bytes

string_max_size_bytes is a parameter to define the maximum byte allowable in string functions or operators. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 1,048,576(1M). The minimum value is 64 and the maximum value is 33,554,432(32M).

The functions and operators affected by this parameter are as follows:

• SPACE()
• CONCAT()
• CONCAT_WS()
• '+': Operand of string
• REPEAT()
• GROUP_CONCAT(): This function is affected not only by string_max_size_bytes parameter, but also by group_concat_max_len.
• INSERT() function

unicode_input_normalization

unicode_input_normalization is a parameter to determine whether or not to input unicode stored in system level or not. The default value is no.

In general, unicode text can be stored in "fully composed" or "fully decomposed". When character 'Ä' has 00C4 (if it is encoded in UTF-8, it becomes 2 bytes of C3 84) which is only one code point. In "fully decomposed" mode, it has two code points/characters. It is 0041 (character "A") and 0308(COMBINING DIAERESIS). In case of UTF-8 encoding, it becomes 3 bytes of 41 CC 88.

CUBRID can work with fully composed unicode. For clients which have fully decomposed texts, configure the value of unicode_input_normalization to yes so that it can be converted to fully composed mode; and then it can be reverted to fully decomposed mode. For normalization of unicode encapsulation of CUBRID, compatibility equivalence is not applied. In general, normalization of unicode is not possible to revert after composition, CUBRID supports revert for characters as many as possible, and it applies normalization of unicode encapsulation. The characteristics of CUBRID normalization are as follows:

• In case of language specific, normalization does not depend on locale.

  If one or more locale can be used, this means every CAS/CSQL process, not CUBRID server. The unicode_input_normalization system parameter determines whether composition of input codes by normalization in system level. The unicode_output_normalization system parameter determines whether composition of output codes by normalization in system level.

• Collation and normalization does not have direct relationship.

  Even though the value of unicode_input_normalization is no, the string of extensible collation (utf8_de_exp, utf8_jap_exp, utf8_km_exp) is properly sorted fully decomposed mode, it is not intended; it is side-effect of UCA(Unicode Collation Algorithm). The extensible collation is implemented only with fully composed texts.

• In CUBRID, composition and decomposition for normalization does not work separately.

  It is generally used when unicode_input_normalization and unicode_output_normalization are yes. In this case, codes entered from clients are stored in composed mode and output in decomposed mode.

If the application client sends the decomposed text data into CUBRID, let CUBRID deal with the composed code, by setting unicode_input_normalization as yes.

If the application client can deal with the decomposed text data only, let CUBRID always send the decomposed code, by setting unicode_output_normalization as yes.

If the application client knows both of input and output, leave the setting unicode_input_normalization and unicode_output_normalization as no.

For more details, see An Overview of Globalization.

unicode_output_normalization

unicode_output_normalization is a parameter to determine whether or not to output unicode stored in system level. The default value is no. For details, see the above unicode_input_normalization description.

update_use_attribute_references

update_use_attribute_references is a parameter whether a value X of a column to be updated in an UPDATE statement affects to another column's update which uses X. The default is no.

The below result of an UPDATE statement is dependent on the value of update_use_attribute_references parameter.

CREATE TABLE tbl(a INT, b INT);
INSERT INTO tbl values (10, NULL);

UPDATE tbl SET a=1, b=a;

If this parameter's value is yes, the updated value of the column "b" will be "1" which is affected by "a=1".

SELECT * FROM tbl;

1, 1

If this parameter's value is no, the updated value of the column "b" will be "NULL", which is affected by the value of "a" stored in the record, not by "a=1".

SELECT * FROM tbl;

1, NULL

Timezone Parameter¶

The following are the parameters related to timezone. The type and the value range for each parameter are as follows:

• timezone

  Specifies a timezone for a session. The default is a value of server_timezone. This value can be specified by a timezone offset (e.g. +01:00, +02) or a timezone region name (e.g. Asia/Seoul). This value can be changed during operating database.

• server_timezone

  Specifies a timezone for a server. The default is a OS timezone. To apply the changed value, database should be restarted. The timezone of operating system is read depending on the operating system and information found in operating system configuration files:

  • on Windows, the API tzset() function and tzname[0] variable are used to retrieve an Windows style timezone name. This name is translated into IANA/CUBRID style name using the CUBRID mapping data (the mapping file is %CUBRID%\timezones\tzdata\windowsZones.xml).
  • on Linux, CUBRID attempts to read and parse the file "/etc/sysconfig/clock". If this file is not available, then the value of link "/etc/localtime" is read and used.
  • on AIX, the value of "TZ" operating system environment variable is used.

  On all operating systems, if the server_timezone is not specified, and the value for timezone from operating system cannot be read, then "Asia/Seoul" zone is used as server timezone.

• tz_leap_second_support

  Specifies to support a leap second or not as yes or no. The default is no.

  A leap second is a one-second adjustment that is occasionally applied to Coordinated Universal Time (UTC) in order to keep its time of day close to the mean solar time.

  To apply the changed value, database should be restarted.

Query Plan Cache-Related Parameters¶

The following are parameters related to the query plan cache functionality. The type and value range for each parameter are as follows:

max_plan_cache_entries

max_plan_cache_entries is a parameter to configure the maximum number of query plans to be cached in the memory. If the max_plan_cache_entries parameter is configured to -1 or 0, generated query plans are not stored in the memory cache; if it is configured to an integer value equal to or greater than 1, a specified number of query plans are cached in the memory.

The following example shows how to cache up to 1,000 queries.

max_plan_cache_entries=1000

max_filter_pred_cache_entries

max_filter_pred_cache_entries is a parameter used to specify the maximum number of filtered index expressions. The filtered index expressions are stored with them complied and can be immediately used in server. If it is not stored in cache, the process is required which filtered index expressions are fetched from database schema and interpreted.

Utility-Related Parameters¶

The following are parameters related to utilities used in CUBRID. The type and value range for each parameter are as follows:

backup_volume_max_size_bytes

backup_volume_max_size_bytes is a parameter to configure the size of the backup volume file created by the cubrid backupdb utility in byte unit. You can set a unit as B, K, M, G or T, which stands for bytes, kilobytes(KB), megabytes(MB), gigabytes(GB) or terabytes(TB) respectively. If you omit the unit, bytes will be applied. The default value is 0, and the minimum value is 32K.

If the parameter is configured to 0, which is the default value, the created backup volume is not partitioned; if it is configured to a value greater than 0, the backup volume is partitioned as much as it is specified size.

communication_histogram

communication_histogram is a parameter associated with Session Commands ";.h" of the CSQL Interpreter and the default value is no. For details, see Dumping CSQL execution statistics information.

compactdb_page_reclaim_only

compactdb_page_reclaim_only is a parameter to configure the compactdb utility, which compacts the storage of already deleted objects to reuse OIDs of the already assigned storage. Storage optimization with the compactdb utility can be divided into three steps. The optimization steps can be selected through the compactdb_page_reclaim_only parameter. If the parameter is configured to 0, which is the default value, step 1, 2 and 3 are all performed, so the storage is optimized in data, table and file units. If it is configured to 1, step 1 is skipped to have the storage optimized in table and file units. If it is configured to 2, steps 1 and 2 are skipped to have the storage optimized only in file units.

• Step 1: Optimizes the storage only in data unit.
• Step 2: Optimizes the storage in table unit.
• Step 3: Optimizes the storage in file (heap file) unit.

csql_history_num

csql_history_num is a parameter to configure the CSQL Interpreter and the number of SQL statements to be stored in the history of the CSQL Interpreter. The default value is 50.

HA-Related Parameters¶

The following are HA-related parameters. The type and value range for each parameter are as follows:

ha_mode

The ha_mode parameter is used to set CUBRID HA, and the default value is off.

• off : CUBRID HA is not used.
• on : CUBRID HA is used using the configured node as a node for failover.
• replica : CUBRID HA is used without using the configured node as a node for failover.

To use the CUBRID HA feature, you should set HA-related parameters in the cubrid_ha.conf file in addition to the ha_mode parameter. For details, see CUBRID HA.

Other Parameters¶

The following are other parameters. The type and value range for each parameter are as follows:

access_ip_control

access_ip_control is a parameter to configure whether to use feature limiting the IP addresses that allow server access. The default value is no. For details, see Limiting Database Server Access.

access_ip_control_file

access_ip_control_file is a parameter to configure the file name in which the list of IP addresses allowed by servers is stored. If access_ip_control value is set to yes, database server allows the list of IP addresses only stored in the file specified by this parameter. For details, see Limiting Database Server Access.

agg_hash_respect_order

agg_hash_respect_order is a parameter to configure whether the groups in an aggregate function will be returned ordered or not. The default is yes. As a reference, see max_agg_hash_size.

If all the groups (keys and accumulators) can fit into hash memory, then "agg_hash_respect_order=no" will skip sorting them before writing to output, so it is fair to assume that the order cannot be guaranteed in this case. However, when overflows occur, then a sort step must be performed and you will get the results in-order even with "agg_hash_respect_order=no".

auto_restart_server

auto_restart_server is a parameter to configure whether to restart the process when it stops due to fatal errors being occurred in database server process. If auto_restart_server value is set to yes, the server process automatically restarts when it has stopped due to errors; it does not restart in case it stops by following normal process (by using STOP command).

enable_string_compression

enable_string_compression is a parameter to configure whether string compression should be used when storing variable string type value into heap, index or list. If enable_string_compression value is set to yes, and the string is at least 255 bytes in size and the compressed string requires less size than original string, then the string is stored in compressed form.

index_scan_in_oid_order

index_scan_in_oid_order is a parameter to configure the result data to be retrieved in OID order after the index scan. If the parameter is set to no, which is the default value, results are retrieved in data order; if it is set to yes, they are retrieved in OID order.

index_unfill_factor

If there is no free space because index pages are full when the INSERT or UPDATE operation is executed after the first index is created, the split of index page nodes occurs. This substantially affects the performance by increasing the operation time. index_unfill_factor is a parameter to configure the percent of free space defined for each index page node when an index is created. The index_unfill_factor value is applied only when an index is created for the first time. The percent of free space defined for the page is not maintained dynamically. Its value ranges between 0 and 0.5. The default value is 0.05.

If an index is created without any free space for the index page node (index_unfill_factor is set to 0), the split of index page nodes occurs every time an additional insertion is made. This may degrade the performance.

If the value of index_unfill_factor is large, a large amount of free space is available when an index is created. Therefore, better performance can be obtained because the split of index nodes does not occur for a relatively long period of time until the free space for the nodes is filled after the first index is created.

If this value is small, the amount of free space for the nodes is small when an index is created. Therefore, it is likely that the index nodes are spilt by INSERT or UPDATE because free space for the index nodes is filled in a short period of time.

java_stored_procedure

java_stored_procedure is a parameter to configure whether to use Java stored procedures by running the Java Virtual Machine (JVM). If the parameter is set to no, which is the default value, JVM is not executed; if it is set to yes, JVM is executed so you can use Java stored procedures. Therefore, configure the parameter to yes if you plan to use Java stored procedures.

multi_range_optimization_limit

If the number of rows specified by the LIMIT clause in the query, which has multiple ranges (col IN (?, ?, ... ,?)) and is available to use an index, is within the number specified in the multi_range_optimization_limit parameter, the optimization for the way of index sorting will be performed. The default value is 100.

For example, if a value for this parameter is set to 50, LIMIT 10 means that it is within the value specified by this parameter, so that the values that meet the conditions will be sorted to produce the result. If LIMIT is 60, it means that it exceeds the parameter configuration value, so that it gets and sorts out all values that meet the conditions.

Depending on the setting value, the differences are made between collecting the result with on-the-fly sorting of the intermediate values and sorting the result values after collecting them, and the bigger value could make more unfavorable performance.

optimizer_enable_merge_join

optimizer_enable_merge_join is a parameter to specify whether to include sort merge join plan as a candidate of query plans or not. The default is no. Regarding sort merge join, see Using SQL Hint.

pthread_scope_process

pthread_scope_process is a parameter to configure the contention scope of threads. It only applies to AIX systems. If the parameter is set to no, the contention scope becomes PTHREAD_SCOPE_SYSTEM; if it is set to yes, it becomes PTHREAD_SCOPE_PROCESS. The default value is yes.

server

server is a parameter used to register the name of database server process which will run automatically when CUBRID server starts.

service

service is a parameter to configure process that starts automatically when the CUBRID service starts. There are four types of processes: server, broker, manager, and heartbeat. Three processes are usually registered as in service=server,broker,manager.

• If the parameter is set to server, the database process specified by the @server parameter gets started.
• If the parameter is set to broker, the broker process gets started.
• If the parameter is set to manager, the manager process gets started.
• If the parameter is set to heartbeat, the HA-related processes get started.

session_state_timeout

session_state_timeout is a parameter used to define how long the CUBRID session data will be kept. The session data will be deleted when the driver terminates the connection or the session time expires. The session time will expire after the specified time if a client terminates abnormally.

Custom variables defined by SET and PREPARE statements can be deleted by DROP / DEALLOCATE statements before session timeout.

The default value is 21,600 seconds(6 hours), and this parameter's unit is second.

sort_limit_max_count

Regarding SORT-LIMIT optimization which can be applied when top-N rows are sorted by "ORDER BY ... LIMIT N" statement, this parameter specifies the LIMIT count to limit applying this optimization. When the value of N is smaller than a value of sort_limit_max_count, SORT-LIMIT optimization is applied. The default is 1000, the minimum is 0(which means that this optimization is disabled), and the maximum is INT_MAX.

For more details, see SORT-LIMIT optimization.

sql_trace_slow

sql_trace_slow is a parameter to configure the execution time of a query which will be judged as a long time execution. You can set a unit as ms, s, min or h, which stands for milliseconds, seconds, minutes or hours respectively. If you omit the unit, milliseconds(ms) will be applied. The default value is -1 and the maximum value is 86,400,000 msec (24 hour). -1 means that the infinite time, so any queries will not be judged as a long duration query. For details, see the below sql_trace_execution_plan.

Note

The system parameter sql_trace_slow judges the query execution time based on the server, but the broker parameter MAX_QUERY_TIMEOUT judges the query execution time based on the broker.

sql_trace_execution_plan

sql_trace_execution_plan is a parameter to configure if the query plan of the long running query is written to the log or not. The default value is no.

If it is set to yes, a long running SQL, a query plan and the output of cubrid statdump command are written to the server error log file(located on $CUBRID/log/server directory) and CAS log file(located on $CUBRID/log/broker/sql_log directory) .

If it is set to no, only a long running SQL is written to the server error log file and CAS log file, and this SQL is displayed when you execute cubrid statdump command.

For example, if you want to write the execution plan of the slow query to the log file, and specify the query which executes more than 5 seconds as the slow query, then configure the value of the sql_trace_slow parameter as 5000(ms) and configure the value of the sql_trace_execution_plan parameter as yes.

But, on the server error log file, the related information is written only when the value of error_log_level is NOTIFICATION.

use_orderby_sort_limit

use_orderby_sort_limit is a parameter to configure whether to keep the intermediate result of sorting and merging process in the statement including the ORDER BY ... LIMIT row_count clause as many as row_count. If it is set to yes, you can decrease unnecessary comparing and merging processes because as many as intermediate results will be kept as the value of row_count. The default value is yes.

vacuum_prefetch_log_mode

vacuum_prefetch_log_mode is a parameter to configure the prefetch mode of log pages on behalf of vacuum.

In mode 0, the vacuum master thread prefetch the required log pages in a shared buffer. In mode 1 (default), each vacuum worker prefetches the required log pages in its own buffer. Mode 0 also requires that vacuum_prefetch_log_buffer_size system parameter is configured, in mode 0 this parameter is ignored and each vacuum worker prefetches an entire vacuum log block (default 32 log pages).

vacuum_prefetch_log_buffer_size

vacuum_prefetch_log_buffer_size is a parameter to configure the log prefetch buffer size of vacuum (it is used only if vacuum_prefetch_log_mode is set to 0).

data_buffer_neighbor_flush_pages

data_buffer_neighbor_flush_pages is a parameter to control the number of neighbor pages to be flushed with background flush (victim candidates flushing). When is less or equal to 1, the neighbor flush feature is considered deactivated.

data_buffer_neighbor_flush_nondirty

data_buffer_neighbor_flush_nondirty is a parameter to control the flushing of non-dirty neighbor pages. When victim candidates pages are flushed, and neighbor flush is activated (data_buffer_neighbor_flush_pages is greater than 1), than single non-dirty pages which completes a chain of neighbor (dirty) pages are also flushed.

cubrid_broker.conf Configuration File and Default Parameters¶

[broker]
MASTER_SHM_ID           =30001
ADMIN_LOG_FILE          =log/broker/cubrid_broker.log

[%query_editor]
SERVICE                 =ON
BROKER_PORT             =30000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =30000
LOG_DIR                 =log/broker/sql_log
ERROR_LOG_DIR           =log/broker/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO

[%BROKER1]
SERVICE                 =ON
BROKER_PORT             =33000
MIN_NUM_APPL_SERVER     =5
MAX_NUM_APPL_SERVER     =40
APPL_SERVER_SHM_ID      =33000
LOG_DIR                 =log/broker/sql_log
ERROR_LOG_DIR           =log/broker/error_log
SQL_LOG                 =ON
TIME_TO_KILL            =120
SESSION_TIMEOUT         =300
KEEP_CONNECTION         =AUTO

Common Parameters¶

The following are parameters commonly applied to entire brokers; it is written under [broker] section.

Parameter by Broker¶

The following describes parameters to configure the environment variables of brokers; each parameter is located under [%broker_name]. The maximum length of broker_name is 63 characters in English.